﻿using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace IDAdmin.Lib.DataLayer
{
    public class NauRechardWebDB
    {
        public class NRAmount
        {
            public DateTime DateTime { get; set; }
            public string Account { get; set; }
            public string Amount { get; set; }
        }
        public List<NauRechardDTO> getStatsData(List<DateTime> dateTimeList)
        {
            List<NauRechardDTO> result = new List<NauRechardDTO>();

            foreach (DateTime dt in dateTimeList)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{0}' and '{1}' and n.datetime between '{2}' and '{3}'";
                        commandText = string.Format(commandText, dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(6).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        object amount = command.ExecuteScalar();
                        string amountStr;
                        if (amount != null)
                        {
                            amountStr = amount.ToString();
                            NauRechardDTO dto = new NauRechardDTO();
                            dto.DateTime = dt;
                            dto.DateTimeStr = dt.ToString("dd/MM/yyyy");
                            dto.ticks = dt.Ticks;
                            //dto.NauRechardNum = amountStr;
                            result.Add(dto);
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

            return result;
        }

        public string getNau(DateTime date)
        {
            string amount = "";
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                string commandText = "select COUNT(DISTINCT(account)) as amount  from nau where datetime between '{0}' and '{1}'";
                commandText = string.Format(commandText, date.ToString("yyyy-MM-dd") + " 00:00:00", date.ToString("yyyy-MM-dd") + " 23:59:59");
                SqlCommand cmd = new SqlCommand(commandText, connection);
                cmd.CommandTimeout = 3600;
                Object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    amount = result.ToString();
                }
            }
            return amount;
        }

        public List<NRAmount> getNauRechar1Amount(DateTime dt)
        {
            List<NRAmount> nauRechard1AmountList = new List<NRAmount>();
            TimeSpan difference = DateTime.Now.Date - dt.Date;
            int days = (int)difference.TotalDays;
            if (days >= 8)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select distinct(fc.account),(select sum(rg.gosu) from FilledCard as fc3 join RatioGosu as rg on fc3.point=rg.phieudiem where fc3.filltime>='{0}' and fc3.account=fc.account) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{1}' and '{2}' and n.datetime between '{3}' and '{4}'";
                        commandText = string.Format(commandText, dt.ToString("yyyy-MM-dd") + " 00:00:00",dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(6).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            nauRechard1AmountList.Add(new NRAmount() { Account = reader.GetValue(0).ToString(), Amount = reader.GetValue(1).ToString(),DateTime=dt });
                        }
                        reader.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return nauRechard1AmountList;
        }
        public string getNauRechard1(DateTime dt)
        {
            string nauRechard1 = "";
            TimeSpan difference = DateTime.Now.Date - dt.Date;
            int days = (int)difference.TotalDays;
            if (days >= 8)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{0}' and '{1}' and n.datetime between '{2}' and '{3}'";
                        commandText = string.Format(commandText, dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(6).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        object amount = command.ExecuteScalar();
                        if (amount != null)
                        {
                            nauRechard1 = amount.ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return nauRechard1;
        }

        public string getNauRechard2(DateTime dt)
        {
            string nauRechard2 = "";
            TimeSpan difference = DateTime.Now.Date - dt.Date;
            int days = (int)difference.TotalDays;
            if (days >= 14)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{0}' and '{1}' and n.datetime between '{2}' and '{3}'";
                        commandText = string.Format(commandText, dt.AddDays(7).ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(13).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        object amount = command.ExecuteScalar();
                        if (amount != null)
                        {
                            nauRechard2 = amount.ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return nauRechard2;
        }

        public string getNauRechard3(DateTime dt)
        {
            string nauRechard3 = "";
            TimeSpan difference = DateTime.Now.Date - dt.Date;
            int days = (int)difference.TotalDays;
            if (days >= 30)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{0}' and '{1}' and n.datetime between '{2}' and '{3}'";
                        commandText = string.Format(commandText, dt.AddDays(14).ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(29).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        object amount = command.ExecuteScalar();
                        if (amount != null)
                        {
                            nauRechard3 = amount.ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return nauRechard3;
        }

        public string getNauRechard4(DateTime dt)
        {
            string nauRechard4 = "";
            TimeSpan difference = DateTime.Now.Date - dt.Date;
            int days = (int)difference.TotalDays;
            if (days >= 60)
            {
                try
                {
                    using (SqlConnection conn = ConnectionHelper.GetConnection())
                    {
                        SqlCommand command = new SqlCommand();
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.Text;
                        command.Connection = conn;
                        string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime between '{0}' and '{1}' and n.datetime between '{2}' and '{3}'";
                        commandText = string.Format(commandText, dt.AddDays(29).ToString("yyyy-MM-dd") + " 00:00:00", dt.AddDays(59).ToString("yyyy-MM-dd") + " 23:59:59", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                        command.CommandText = commandText;
                        object amount = command.ExecuteScalar();
                        if (amount != null)
                        {
                            nauRechard4 = amount.ToString();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return nauRechard4;
        }

        public string getNauRechard5(DateTime dt)
        {
            string nauRechard5 = "";
            try
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.CommandTimeout = 3600;
                    command.CommandType = CommandType.Text;
                    command.Connection = conn;
                    string commandText = "select count(distinct(fc.account)) as amount from FilledCard fc,NAU n where fc.account=n.account and fc.filltime>= {0} and n.datetime between '{1}' and '{2}'";
                    commandText = string.Format(commandText, dt.AddDays(59).ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 00:00:00", dt.ToString("yyyy-MM-dd") + " 23:59:59");
                    command.CommandText = commandText;
                    object amount = command.ExecuteScalar();
                    if (amount != null)
                    {
                        nauRechard5 = amount.ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return nauRechard5;
        }

        public void insertNauRechard(List<NauRechardDTO> data, List<DateTime> dateTimeList)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                try
                {
                    SqlCommand command = new SqlCommand("sp_delete_NauRechard", conn);
                    command.CommandTimeout = 3600;
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddWithValue("@fromDT", dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00");
                    command.Parameters.AddWithValue("@toDT", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {

                }
            }
            foreach (NauRechardDTO item in data)
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    try
                    {
                        SqlCommand command = new SqlCommand("sp_Insert_NauRechard", conn);
                        command.CommandTimeout = 3600;
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@datetime", item.DateTime);
                        command.Parameters.AddWithValue("@nau", item.Nau);
                        command.Parameters.AddWithValue("@naurechard1", item.NauRechard1);
                        command.Parameters.AddWithValue("@naurechard2", item.NauRechard2);
                        command.Parameters.AddWithValue("@naurechard3", item.NauRechard3);
                        command.Parameters.AddWithValue("@naurechard4", item.NauRechard4);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
        }
        public List<NauRechardDTO> getFastNauRechardData(string from, string to)
        {
            List<NauRechardDTO> data = new List<NauRechardDTO>();
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand command = new SqlCommand("sp_Select_NauRechard", conn);
                command.CommandTimeout = 3600;
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@fromDT", from);
                command.Parameters.AddWithValue("@toDT", to);
                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    NauRechardDTO nr = new NauRechardDTO();
                    nr.ticks = dataReader.GetDateTime(0).Ticks;
                    nr.DateTimeStr = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                    nr.Nau = dataReader.GetValue(1).ToString();
                    nr.NauRechard1 = dataReader.GetValue(2).ToString();
                    nr.NauRechard2 = dataReader.GetValue(3).ToString();
                    nr.NauRechard3 = dataReader.GetValue(4).ToString();
                    nr.NauRechard4 = dataReader.GetValue(5).ToString();
                    data.Add(nr);
                }
                dataReader.Close();
            }
            return data;
        }
    }
}
